package com.sunda.spmswms.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sunda.spmswms.entity.SapPackageList;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * SAP装箱单表抬头 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-04-22
 */
@Mapper
public interface SapPackageListMapper extends BaseMapper<SapPackageList> {

    @Select("<script>SELECT\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PL.PACKAGE_LIST AS \"packageList\",\n" +
            "       PL.CUSTOMER AS \"customer\",\n" +
            "       PL.SPARE_PARTS AS \"spareParts\",\n" +
            "       PL.PLANNED_CABINETS_QTY AS \"plannedCabinetsQty\",\n" +
            "       PL.FROM_PORT AS \"fromPort\",\n" +
            "       PL.FROM_PORT_NAME AS \"fromPortName\",\n" +
            "       PL.TO_PORT AS \"toPort\",\n" +
            "       PL.TO_PORT_NAME AS \"toPortName\",\n" +
            "       PL.SHIP_COMPANY AS \"shipCompany\",\n" +
            "       PL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PL.PLANNER AS \"planner\",\n" +
            "       PL.LOADER AS \"loader\",\n" +
            "       PL.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       PL.SPMS_STATUS AS \"spmsStatus\"\n" +
            "FROM SAP_PACKAGE_LIST PL WHERE PL.PACKAGE_LIST IN (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    WHERE PLD.LOADING_WERKS\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WERKS FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            "      AND PLD.LOADING_WHS_LOCATION_CODE\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WHS_LOCATION_CODE FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            ") \n" +
            " <when test='packageList!=null'> AND PL.PACKAGE_LIST = #{packageList}</when> \n" +
            " <when test='spmsStatus!=null'> AND PL.SPMS_STATUS = #{spmsStatus}</when> \n" +
            "ORDER BY PL.SPMS_STATUS ASC </script>\n")
    List<Map<String, Object>> getSapPackageList(String userId, String packageList, String spmsStatus);

    @Select("<script>SELECT\n" +
            "       ROWNUM AS \"rowId\",\n" +
            "       PL.PACKAGE_LIST AS \"packageList\",\n" +
            "       PL.CUSTOMER AS \"customer\",\n" +
            "       PL.SPARE_PARTS AS \"spareParts\",\n" +
            "       PL.PLANNED_CABINETS_QTY AS \"plannedCabinetsQty\",\n" +
            "       PL.FROM_PORT AS \"fromPort\",\n" +
            "       PL.FROM_PORT_NAME AS \"fromPortName\",\n" +
            "       PL.TO_PORT AS \"toPort\",\n" +
            "       PL.TO_PORT_NAME AS \"toPortName\",\n" +
            "       PL.SHIP_COMPANY AS \"shipCompany\",\n" +
            "       PL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       PL.PLANNER AS \"planner\",\n" +
            "       PL.LOADER AS \"loader\",\n" +
            "       PL.ACTUAL_LOADING_DATE AS \"actualLoadingDate\",\n" +
            "       PL.SPMS_STATUS AS \"spmsStatus\"\n" +
            "FROM SAP_PACKAGE_LIST PL WHERE PL.PACKAGE_LIST IN (\n" +
            "    SELECT DISTINCT PLD.PACKAGE_LIST\n" +
            "    FROM SAP_PACKAGE_LIST_DTL PLD\n" +
            "    WHERE PLD.LOADING_WERKS\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WERKS FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            "      AND PLD.LOADING_WHS_LOCATION_CODE\n" +
            "              IN (\n" +
            "                  SELECT DISTINCT SUW.WHS_LOCATION_CODE FROM SPMS_USER SU\n" +
            "                      LEFT JOIN SPMS_USER_WHS SUW ON SU.WORK_CODE = SUW.WORK_CODE WHERE SU.WORK_CODE = #{userId})\n" +
            ") \n" +
            " <when test='packageList!=null'> AND PL.PACKAGE_LIST = #{packageList}</when> \n" +
            " <when test='spmsStatus!=null'> AND PL.SPMS_STATUS = #{spmsStatus}</when> \n" +
            "ORDER BY PL.SPMS_STATUS ASC, PL.PACKAGE_LIST DESC </script>\n")
    List<Map<String, Object>> getSapPackageListPage(Page<Map<String, Object>> pages, String userId, String packageList, String spmsStatus);

    /***
     * 装箱单直装卸货入库时，查询获取物料的基本信息，单位换算关系等；
     * @param materialNo
     * @return
     */
    @Select("SELECT SAP.MATERIAL_NO AS \"materialNo\",\n" +
            "       (CASE WHEN SAP.NUMERATOR IS NULL THEN 1 ELSE SAP.NUMERATOR END) AS \"numerator\",\n" +
            "       (CASE WHEN SAP.DENOMINATOR IS NULL THEN 1 ELSE SAP.DENOMINATOR END) AS \"denominator\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\"\n" +
            "FROM MATERIAL_SAP SAP\n" +
            "WHERE SAP.MATERIAL_NO = #{materialNo} ")
    Map<String, Object> getNumeratorDenominator(@Param("materialNo") String materialNo);
}
